﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace DADOS.agenda
{
    public class metAgenda
    {

        DB_PROAD banco = new DB_PROAD();

        private SqlConnection _conexao = new SqlConnection(ConfigurationManager.ConnectionStrings["DB_PROAD_SQL"].ConnectionString);

        private void FecharConexao(object banco)
        {
            ((DB_PROAD)banco).Connection.Close();
        }

        private void InicializarConexao(object banco)
        {
            ((DB_PROAD)banco).Connection.Open();
        }
        
        public TB_AGENDA incluirAgenda(TB_AGENDA objAgenda)
        {
            try
            {
                var db = new DB_PROAD();
                db.AddToTB_AGENDA(objAgenda);
                db.SaveChanges();
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                
            }

            return objAgenda;
        }

        public TB_AGENDA VerificarUltAgenda(int intCodUsuario)
        {
            var objAgenda = new TB_AGENDA();
            try
            {
                InicializarConexao(banco);
                objAgenda = banco.TB_AGENDA.Where(q => q.CO_USUARIO == intCodUsuario && q.NR_STATUS == 0).OrderBy(q => q.DT_AGENDA).FirstOrDefault();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                FecharConexao(banco);
            }

            return objAgenda;
        }

        public TB_USUARIO PegarUusarioAgenda(int intCodAgenda)
        {
            var objUsuario = new TB_USUARIO();
            try
            {
                InicializarConexao(banco);
                var objAgenda = banco.TB_AGENDA.Where(q => q.CO_AGENDA == intCodAgenda).FirstOrDefault();
                objUsuario = banco.TB_USUARIO.Where(q => q.CO_USUARIO == objAgenda.CO_USUARIO).FirstOrDefault();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                FecharConexao(banco);
            }

            return objUsuario;
        }

        public void FinalizarAgenda(int intCodAgenda, int intCodProfissional)
        {
            try
            {
                InicializarConexao(banco);

                var objAgenda = banco.TB_AGENDA.Where(q => q.CO_AGENDA == intCodAgenda).FirstOrDefault();
                if (objAgenda != null)
                {
                    //Alterando Agenda e mudando o status para finalizado
                    objAgenda.NR_STATUS = 3;
                    objAgenda.DT_ATENDIMENTO = DateTime.Now;
                    objAgenda.DT_ATUALIZACAO = DateTime.Now;
                    objAgenda.CO_PROFISSIONAL = intCodProfissional;

                    banco.SaveChanges();
                }


            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                FecharConexao(banco);
            }
        }

        public TB_AGENDA PegarAgenda(int intCodAgenda)
        {
            try
            {
                InicializarConexao(banco);
                var objAgenda = banco.TB_AGENDA.Where(q => q.CO_AGENDA == intCodAgenda).FirstOrDefault();
                return objAgenda;
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                FecharConexao(banco);
            }
        }

        public TB_AGENDA RemarcarAgenda(int intCodAgenda, DateTime dtNovaData, int intCodProfissional)
        {
            var objNovaAgenda = new TB_AGENDA();
            try
            {
                InicializarConexao(banco);

                var objAgenda = banco.TB_AGENDA.Where(q => q.CO_AGENDA == intCodAgenda).FirstOrDefault();
                if(objAgenda != null)
                {
                    //Alterando Agenda e mudando o status para remarcado
                    objAgenda.NR_STATUS = 1;
                    objAgenda.DT_ATUALIZACAO = DateTime.Now;
                    objAgenda.CO_PROFISSIONAL = intCodProfissional;
                    objAgenda.DS_OBS = "Agenda remarcada para o dia " + dtNovaData;

                    //Criando uma nova agenda
                    objNovaAgenda.CO_PROFISSIONAL = intCodProfissional;
                    objNovaAgenda.CO_USUARIO = objAgenda.CO_USUARIO;
                    objNovaAgenda.DT_AGENDA = dtNovaData;
                    objNovaAgenda.NR_STATUS = 0;

                    //Salvando tudo
                    banco.AddToTB_AGENDA(objNovaAgenda);

                    banco.SaveChanges();
                }

                
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                FecharConexao(banco);
            }

            return objNovaAgenda;
        }

        public void CancelarAgenda(int intCodAgenda, string strDesc)
        {
            try
            {
                InicializarConexao(banco);

                var objAgenda = banco.TB_AGENDA.Where(q => q.CO_AGENDA == intCodAgenda).FirstOrDefault();
                if(objAgenda != null)
                {
                    objAgenda.NR_STATUS = 2;
                    objAgenda.DT_ATUALIZACAO = DateTime.Now;
                    objAgenda.DS_OBS = strDesc;
                    banco.SaveChanges();
                }

                
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                FecharConexao(banco);
            }
        }

        public List<classStatusAgenda> ListaStatsAgenda()
        {
            var listAgenda = new List<classStatusAgenda>();

            var objstatus0 = new classStatusAgenda();
            objstatus0.CO_STATUS_AGENDA = 0;
            objstatus0.NO_STATUS_AGENDA = "Aguardando Atendimento";
            listAgenda.Add(objstatus0);

            var objstatus1 = new classStatusAgenda();
            objstatus1.CO_STATUS_AGENDA = 1;
            objstatus1.NO_STATUS_AGENDA = "Remarcada";
            listAgenda.Add(objstatus1);

            var objstatus2 = new classStatusAgenda();
            objstatus2.CO_STATUS_AGENDA = 2;
            objstatus2.NO_STATUS_AGENDA = "Cancelada";
            listAgenda.Add(objstatus2);

            var objstatus3 = new classStatusAgenda();
            objstatus3.CO_STATUS_AGENDA = 3;
            objstatus3.NO_STATUS_AGENDA = "Atendimento Finalizado";
            listAgenda.Add(objstatus3);

            return listAgenda;
        }

        public DataSet ListarAgendaEquipeTop10(string strCodEs)
        {
            DataSet ds = new DataSet();

            string Query = "SELECT TOP 10 CONVERT(VARCHAR,AG.DT_AGENDA,103) AS DT_AGENDA,";
            Query += "              USU.NO_NOME, USU.CO_USUARIO, AG.CO_AGENDA FROM DB_PROAD..TB_AGENDA AS AG";
	        Query += "                INNER JOIN DB_PROAD..TB_USUARIO AS USU";
            Query += "                    ON USU.CO_USUARIO = AG.CO_USUARIO";
            Query += "                INNER JOIN DB_PROAD..TB_ES AS ES";
            Query += "                    ON ES.CO_ES = USU.CO_ES";
            Query += "                WHERE ES.CO_ES = " + strCodEs;
            Query += "                AND AG.NR_STATUS = 0";
            Query += "                ORDER BY AG.DT_AGENDA";

            
            
            SqlCommand cmd = new SqlCommand(Query, _conexao);

            cmd.Connection = _conexao;

            if (_conexao.State == ConnectionState.Closed)
            {
                _conexao.Open();
            }
            SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
            dataAdapter.Fill(ds);

            _conexao.Close();

            return ds;
        }

        public DataSet ListarAgendaEquipe(string strCodEs)
        {
            DataSet ds = new DataSet();

            string Query = "SELECT CONVERT(VARCHAR,AG.DT_AGENDA,103) AS DT_AGENDA,";
            Query += "              USU.NO_NOME, USU.CO_USUARIO, AG.CO_AGENDA FROM DB_PROAD..TB_AGENDA AS AG";
            Query += "                INNER JOIN DB_PROAD..TB_USUARIO AS USU";
            Query += "                    ON USU.CO_USUARIO = AG.CO_USUARIO";
            Query += "                INNER JOIN DB_PROAD..TB_ES AS ES";
            Query += "                    ON ES.CO_ES = USU.CO_ES";
            Query += "                WHERE ES.CO_ES = " + strCodEs;
            Query += "                AND AG.NR_STATUS = 0";
            Query += "                ORDER BY AG.DT_AGENDA";



            SqlCommand cmd = new SqlCommand(Query, _conexao);

            cmd.Connection = _conexao;

            if (_conexao.State == ConnectionState.Closed)
            {
                _conexao.Open();
            }
            SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
            dataAdapter.Fill(ds);

            _conexao.Close();

            return ds;
        }

        public DataSet ListarAgendaEquipeSel(string strCodEs, string strDtSelecionada)
        {
            DataSet ds = new DataSet();

            string Query = "SELECT CONVERT(VARCHAR,AG.DT_AGENDA,103) AS DT_AGENDA,";
            Query += "              USU.NO_NOME, USU.CO_USUARIO, AG.CO_AGENDA FROM DB_PROAD..TB_AGENDA AS AG";
            Query += "                INNER JOIN DB_PROAD..TB_USUARIO AS USU";
            Query += "                    ON USU.CO_USUARIO = AG.CO_USUARIO";
            Query += "                INNER JOIN DB_PROAD..TB_ES AS ES";
            Query += "                    ON ES.CO_ES = USU.CO_ES";
            Query += "                WHERE ES.CO_ES = " + strCodEs;
            Query += "                AND AG.NR_STATUS = 0";
            Query += "                AND CONVERT(VARCHAR,AG.DT_AGENDA,103) = '" + strDtSelecionada + "'";
            Query += "                ORDER BY AG.DT_AGENDA";



            SqlCommand cmd = new SqlCommand(Query, _conexao);

            cmd.Connection = _conexao;

            if (_conexao.State == ConnectionState.Closed)
            {
                _conexao.Open();
            }
            SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
            dataAdapter.Fill(ds);

            _conexao.Close();

            return ds;
        }
    }
}
